package cn.pzhu.java.ch06.dbapp;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class UserDao {
	final static String url = "jdbc:mysql://localhost:3306/javadb?useUnicode=true&characterEncoding=UTF-8";
	final static String user = "root";
	final static String password = "123456";
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private Connection getConnection() throws SQLException {
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	public boolean updateUser(User user){
		boolean flag=false;
		String sql="UPDATE Users SET NAME=?,AGE=? WHERE ID=?";
		Connection conn=null;
		PreparedStatement pst=null;
		try {
			conn=getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1,user.getName());
			pst.setInt(2, user.getAge());
			pst.setLong(3, user.getId());
			flag=(pst.executeUpdate()==1);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(pst!=null)pst.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	public User  queryUserById(long id){
		User user=null;
		String sql="SELECT  ID,NAME,AGE FROM Users WHERE ID=?";
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		
		try {
			conn=getConnection();
			pst=conn.prepareStatement(sql);
			pst.setLong(1, id);
			rs=pst.executeQuery();
			if(rs.next()){
				user=new User(rs.getLong("ID"),
						rs.getString("NAME"),
						rs.getInt("AGE"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)rs.close();
				if(pst!=null)pst.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return user;
		
	}
	public boolean addUser(User user){
		boolean flag=false;
		String sql="INSERT INTO Users(NAME,AGE)VALUES(?,?)";
		Connection conn=null;
		PreparedStatement pst=null;
		try {
			conn=getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1,user.getName());
			pst.setInt(2, user.getAge());
			flag=(pst.executeUpdate()==1);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(pst!=null)pst.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return flag;
	}
	public List<User>  queryUserByName(String name){
		List<User> userList=new ArrayList<User>();
		String sql="SELECT  ID,NAME,AGE FROM Users WHERE NAME  LIKE ?";
		Connection conn=null;
		PreparedStatement pst=null;
		ResultSet rs=null;
		
		try {
			conn=getConnection();
			pst=conn.prepareStatement(sql);
			pst.setString(1, "%"+name+"%");
			rs=pst.executeQuery();
			while(rs.next()){
				User u=new User(rs.getLong("ID"),
						rs.getString("NAME"),
						rs.getInt("AGE"));
				userList.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)rs.close();
				if(pst!=null)pst.close();
				if(conn!=null)conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return userList;
		
	}
}
